package com.wangfugui.mybatisplus.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelRowDeleter {

    public static void main(String[] args) {
        String filePath = "/Users/zhaolele/Documents/mail/test.xlsx";
        int conditionColumnIndex = 0;
        String conditionValue = "DELETE";

        try {
            deleteRowsByCondition(filePath, conditionColumnIndex, conditionValue);
            System.out.println("操作完成，满足条件的行已物理删除并覆盖源文件。");
        } catch (IOException e) {
            System.err.println("处理文件时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static void deleteRowsByCondition(String filePath, int conditionColumnIndex, String conditionValue)
            throws IOException {

        FileInputStream inputStream = new FileInputStream(filePath);
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 记录需要删除的行号
        int lastRowNum = sheet.getLastRowNum();
        int[] rowsToDelete = new int[lastRowNum + 1];
        int deleteCount = 0;

        // 第一次遍历：标记要删除的行
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Cell cell = row.getCell(conditionColumnIndex);
                if (cell != null && conditionValue.equals(getCellValueAsString(cell))) {
                    rowsToDelete[deleteCount++] = i;
                }
            }
        }

        // 第二次遍历：从下往上物理删除行
        for (int i = deleteCount - 1; i >= 0; i--) {
            int rowIndex = rowsToDelete[i];
            if (rowIndex >= 0 && rowIndex <= sheet.getLastRowNum()) {
                sheet.removeRow(sheet.getRow(rowIndex));
                if (rowIndex < sheet.getLastRowNum()) {
                    sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1);
                }
            }
        }

        inputStream.close();

        // 覆盖原文件
        FileOutputStream outputStream = new FileOutputStream(filePath);
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    private static String getCellValueAsString(Cell cell) {
        // 同上
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
}
